/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Outils;

import Servlet.interrogationIP;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.text.DateFormat;
import java.util.Date;
import java.util.Locale;

/**
 *
 * @author SDR
 *
 *
 *
 */
public class LiaisonSqlDB {

    private Connection con;
    private Statement stmt;
    private String host, dbname, login, password;
    private int port;

    public LiaisonSqlDB(String host, String dbname, String login, String password) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            try {
                con = DriverManager.getConnection("jdbc:mysql://" + host + "/" + dbname, login, password);
                // con = DriverManager.getConnection("jdbc:mysql://localhost/arnueldb", "arnuel", "mysqlarnuel");
                stmt = con.createStatement();
                this.host = host;
                this.dbname = dbname;
                this.login = login;
                this.password = password;
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean identifierAdmin(String loginHash, String passHash) {
        try {
            stmt.executeQuery("SELECT * FROM `users` WHERE user= '" + loginHash + "' AND mdp= '" + passHash + "' AND droit= 'yes'");
            ResultSet rs = stmt.getResultSet();

            if (rs.next()) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean identifier(String loginHash, String passHash) {
        try {
            stmt.executeQuery("SELECT * FROM `users` WHERE user= '" + loginHash + "' AND mdp= '" + passHash + "'");
            ResultSet rs = stmt.getResultSet();

            if (rs.next()) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean creationUtilisateur(String loginHash, String passHash, String droit) {
        try {
            Hash hash = new Hash();
            if (loginHash.equalsIgnoreCase(hash.hashMd5("admin")) || passHash.equalsIgnoreCase(hash.hashMd5("admin"))) {
                return false;
            }
            //stmt.executeQuery("SELECT * FROM `users` WHERE user='"+loginHash+"' AND mdp='"+passHash+"'");
            stmt.executeQuery("SELECT * FROM `users` WHERE user='" + loginHash + "'");
            ResultSet rs = stmt.getResultSet();

            if (rs.next()) {
                return false;
            }
            stmt.executeQuery("SELECT * FROM `users`");
            rs = stmt.getResultSet();
            rs.last();
            int idLast = rs.getInt("id_user");
            int idNew = idLast + 1;
            String sql = "INSERT INTO `users` (user, mdp, droit ,id_user) VALUES ('" + loginHash + "', '" + passHash + "'" + ",'" + droit + "','" + idNew + "')";
            System.out.println(sql);
            stmt.executeUpdate("INSERT INTO `users` (user, mdp, droit ,id_user) VALUES ('" + loginHash + "', '" + passHash + "'" + ",'" + droit + "','" + idNew + "')");

            return true;
        } catch (SQLException ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    public String recupererIPPourUtilisateur(String user) {
        try {
            StringBuffer sb = new StringBuffer();
            //ArrayList<String> temp = new ArrayList<String>();
            //String[] retour;
            boolean bool = true;
            stmt.executeQuery("SELECT * FROM `addresses` WHERE id_user= " + user);
            ResultSet rs = stmt.getResultSet();
            bool = rs.next();
            while (bool) {
                sb.append(rs.getString(1) + " ");
                bool = rs.next();
            }

            return sb.toString();
        } catch (Exception ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return "rien du trou";
        }

    }

    public int recupererUser_ID(String loginHash) {
        try {

            stmt.executeQuery("SELECT id_user FROM `users` WHERE user= '" + loginHash + "'");
            ResultSet rs = stmt.getResultSet();
            rs.next();
            int id = rs.getInt("id_user");
            System.out.println(id);
            return id;
        } catch (Exception ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
    }

    public boolean enregistrerIPpourutilisateur(String id_user, String iP) {
        try {

            stmt.executeQuery("SELECT * FROM `users` WHERE id_user =" + id_user);
            ResultSet rs = stmt.getResultSet();
            if (!rs.next()) {
                return false;
            }

            stmt.executeQuery("SELECT * FROM `addresses` WHERE id_user = " + id_user + " AND ip='" + iP + "'");
            rs = stmt.getResultSet();

            if (rs.next()) {
                return false;
            }

            stmt.executeUpdate("INSERT INTO `addresses` (ip, id_user) VALUES ('" + iP + "', " + id_user + ")");

            return true;
        } catch (Exception ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean supprimerIPpourutilisateur(String id_user, String iP) {
        try {

            stmt.executeQuery("SELECT * FROM `users` WHERE id_user =" + id_user);
            ResultSet rs = stmt.getResultSet();
            if (!rs.next()) {
                return false;
            }

            stmt.executeQuery("SELECT * FROM `addresses` WHERE id_user = " + id_user + " AND ip='" + iP + "'");
            rs = stmt.getResultSet();

            if (!rs.next()) {
                return false;
            }
            stmt.executeUpdate("DELETE FROM `arnueldb`.`addresses`  WHERE `addresses`.`ip` = '" + iP + "' AND `addresses`.`id_user` =" + id_user);

            return true;
        } catch (Exception ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean stockerInformation(int index, String date, String serveur, String methodeName, String methodeResult) {

        try {
            String cmd = "";
            String values = "vide";
            String[] valeurs = methodeResult.split(";");
            if (valeurs.length > 1) {
                for (int i = 0; i < valeurs.length; i++) {
                    if (values.contentEquals("vide")) {
                        values = "('" + index + "','" + date + "','" + serveur + "','" + methodeName + "','" + valeurs[i] + "')";
                        
                    } else if (i != valeurs.length) {
                        values = values + "," + "('" + index + "','" + date + "','" + serveur + "','" + methodeName + "','" + valeurs[i] + "')";
                        
                    } else {
                        
                        values = values + "," + "('" + index + "','" + date + "','" + serveur + "','" + methodeName + "','" + valeurs[i] + "');";

                    }
                }
                cmd = "INSERT INTO `historique` (`id_ind`,`date`,`serveur`,`methodeName`,`methodeResult`) VALUES " + values;

            } else {
                cmd = "INSERT INTO `historique` (`id_ind`,`date`,`serveur`,`methodeName`,`methodeResult`) VALUES ('" + index + "','" + date + "','" + serveur + "','" + methodeName + "','" + methodeResult + "');";
            }
            System.out.println(cmd);
            stmt.executeUpdate(cmd);

            ResultSet rs = stmt.getResultSet();
            if (rs == null) {
                return false;
            }

            return true;
        } catch (Exception ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public String getInformation() {
        String date, serveur, methodeName, resultRaw = "";
        String resultAll = "Historique vide !";
        try {
            String cmd = "select * from historique group by id_ind;";
            stmt.executeQuery(cmd);
            ResultSet rs = stmt.getResultSet();

            while (rs.next()) {
                date = rs.getString(2);
                serveur = rs.getString(3);
                methodeName = rs.getString(4);
                resultRaw = date + "-" + serveur + "-" + methodeName;
                if (resultAll.contentEquals("Historique vide !")) {
                    resultAll = resultRaw;
                } else {
                    resultAll = resultAll + ";" + resultRaw;
                }

            }
            System.out.println(resultAll);
            return resultAll;

        } catch (SQLException ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return "Vide";
        }

    }

    public boolean suppressionLog() {
        try {
            String cmd = "truncate table historique;";
            stmt.executeQuery(cmd);
            ResultSet rs = stmt.getResultSet();
            return true;

        } catch (SQLException ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    public String getInformationMono(String parametre) {
        String result = "Historique vide !";
        String parametres[] = parametre.split("-");
        try {

            System.out.println(parametre);
            System.out.println(parametres[0]);
            System.out.println(parametres[1]);
            System.out.println(parametres[2]);

            String cmd = "select methodeResult from historique where date = '" + parametres[0] + "' and serveur = '" + parametres[1] + "' and methodeName = '" + parametres[2] + "';";
            stmt.executeQuery(cmd);
            ResultSet rs = stmt.getResultSet();

            while (rs.next()) {
                if (result.contentEquals("Historique vide !")) {
                    result = rs.getString(1);
                } else {
                    result = result + ";" + rs.getString(1);
                }

            }

            System.out.println(result);
            return result;

        } catch (SQLException ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return "Vide";
        }
    }

    public int getIndexMax() {
        int index = -1;
        int indexMax = -1;
        try {
            String cmd = "select id_ind from historique group by id_ind;";
            stmt.executeQuery(cmd);
            ResultSet rs = stmt.getResultSet();
            while (rs.next()) {
                index = rs.getInt(1);
                if (index > indexMax) {
                    indexMax = index;
                }
            }

            return indexMax;

        } catch (SQLException ex) {
            Logger.getLogger(LiaisonSqlDB.class.getName()).log(Level.SEVERE, null, ex);
            return -1;
        }
    }
}
